SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q01]          
/*------------------------------------------------*/          
/*-- Empresa :  OFISIS S.A.         --*/          
/*-- Cliente :  OFISIS S.A.         --*/          
/*-- Sistema :  POSICION BANCOS BANCOS      --*/          
/*-- Modulo :  EMPRESA           --*/          
/*-- Programa :  REPORTE DE POSICION EN BANCOS--*/          
/*-- Script :  SP_TCMOVI_BANC_Q01.SQL   --*/          
/*-- Nombre SP :  SP_TCMOVI_BANC_Q01   --*/          
/*-- Desarrollado por  :  D.CH     --*/          
/*-- Fecha :  16/02/05       --*/          
/*-- Base de Datos :  MICROSOFT SQL SERVER 8.0 --*/          
/*------------------------------------------------*/          
/*------------------------------------------------*/          
/*-- MODIFICADO POR 1 :  D,CH.       --*/          
/*-- Fecha   :  16/02/05          --*/          
/*-- DETALLE    :  A SQL           --*/          
/*--                   --*/          
/*------------------------------------------------*/          
/*------------------------------------------------*/          
/*-- MODIFICADO POR 2 :  M.E.C.       --*/          
/*-- Fecha   :  05/01/06          --*/          
/*-- DETALLE    :  Manejo de Fecha Reporte  --*/          
/*--                   --*/          
/*------------------------------------------------*/          
/*-------------------------------------------------*/          
/*--  DROP PROC SP_TCMOVI_BANC_Q01    */          
/*--  GRANT Execute ON SP_TCMOVI_BANC_Q01 TO PUBLIC    */          
/*--  SP_TCMOVI_BANC_Q01 'OFISIS', '01', '01', 'SOL', 'SOL', '05/29/2000'  */          
/*--------------------------------------------------------------*/          
@ISCO_USUA  TD_VC_008,          
@ISCA_WHER_AUXI VARCHAR(600),          
@IDFE_REPO  TD_DT_001  ,         
@ISST_FECH_INGR TD_VC_001,          
@ISST_FECH_EGRE TD_VC_001          
         
        
         
AS          
          
          
-- INGRESOS          
EXECUTE ('          
INSERT INTO #TWPOSI_BANC_Q01_01( CO_EMPR, CO_BANC, NU_CNTA_BANC,           
  IM_INGR, IM_EGRE, IM_CHEQ_CART )          
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC,            
SUM( ISNULL(T1.IM_MOVI,0) ), 0, 0          
From   TCMOVI_BANC T1, TMCNTA_BANC          
Where T1.TI_MOVI_BANC = "I"          
AND T1.TI_SITU IN ("ACT", "EXT")         
AND T1.CO_EMPR = TMCNTA_BANC.CO_EMPR          
AND T1.CO_BANC = TMCNTA_BANC.CO_BANC          
AND T1.NU_CNTA_BANC = TMCNTA_BANC.NU_CNTA_BANC ' + @ISCA_WHER_AUXI + '          
AND (  ( T1.FE_OPER = "' + @IDFE_REPO + '" AND ( "' + @ISST_FECH_INGR + '" = "O" OR ISNULL(T1.NU_CHEQ,"") =  "" ) )          
    OR ( T1.FE_DISP = "' + @IDFE_REPO + '"  AND "' + @ISST_FECH_INGR + '" = "D" AND ISNULL(T1.NU_CHEQ,"") != "" ) )          
AND Exists ( Select CO_EMPR From  TTUSER_CNBA t4          
 Where        T1.CO_EMPR = t4.CO_EMPR          
 And            T1.CO_BANC  = t4.CO_BANC          
 And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC          
 And            t4.CO_USUA = "' + @ISCO_USUA + '"          
 And            t4.ST_REPO = "S")           
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC ' )          
          
-- EGRESOS          
EXECUTE('          
INSERT INTO #TWPOSI_BANC_Q01_01( CO_EMPR, CO_BANC, NU_CNTA_BANC,           
  IM_INGR, IM_EGRE, IM_CHEQ_CART )          
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC,            
0, SUM( ISNULL(T1.IM_MOVI,0) ), 0          
From   TCMOVI_BANC T1, TMCNTA_BANC           
Where  T1.TI_MOVI_BANC = "E"          
AND T1.TI_SITU IN ("ACT", "EXT")       
AND T1.CO_EMPR = TMCNTA_BANC.CO_EMPR          
AND T1.CO_BANC = TMCNTA_BANC.CO_BANC          
AND T1.NU_CNTA_BANC = TMCNTA_BANC.NU_CNTA_BANC ' + @ISCA_WHER_AUXI + '          
AND NOT (          
ISNULL(T1.NU_CHEQ,"") !=  ""          
AND ( T1.FE_CHEQ IS NULL OR ( T1.FE_CHEQ IS NOT NULL  AND T1.FE_CHEQ > "' + @IDFE_REPO + '"  )))          
AND (  ( T1.FE_OPER = "' + @IDFE_REPO + '"  AND ( "' + @ISST_FECH_EGRE + '" = "O" OR NU_CHEQ IS NULL ) )          
    OR ( T1.FE_PROG_CHEQ = "' + @IDFE_REPO + '"  AND "' + @ISST_FECH_EGRE + '" = "P" AND NU_CHEQ IS NOT NULL )           
    OR ( T1.FE_ENTR_CHEQ = "' + @IDFE_REPO + '"  AND "' + @ISST_FECH_EGRE + '" = "E" AND NU_CHEQ IS NOT NULL )           
    OR ( T1.FE_CHEQ = "' + @IDFE_REPO + '"  AND "' + @ISST_FECH_INGR + '" = "C" AND NU_CHEQ IS NOT NULL )  )          
AND Exists ( Select CO_EMPR From  TTUSER_CNBA t4          
 Where        T1.CO_EMPR = t4.CO_EMPR          
 And            T1.CO_BANC  = t4.CO_BANC          
 And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC          
 And            t4.CO_USUA = "' + @ISCO_USUA + '"     And            t4.ST_REPO = "S")           
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC ')          
          
          
-- CHEQUES EN CARTERA          
EXECUTE('          
INSERT INTO #TWPOSI_BANC_Q01_01( CO_EMPR, CO_BANC, NU_CNTA_BANC,           
  IM_INGR, IM_EGRE, IM_CHEQ_CART )          
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC,            
0, 0, SUM( ISNULL(T1.IM_MOVI,0) )          
From   TCMOVI_BANC T1, TMCNTA_BANC          
Where  T1.TI_MOVI_BANC = "E"          
AND T1.TI_SITU IN ("ACT", "EXT")        
AND ISNULL(T1.NU_CHEQ, "") != ""          
AND ( T1.FE_CHEQ IS NULL OR ( T1.FE_CHEQ IS NOT NULL  AND T1.FE_CHEQ > "' + @IDFE_REPO + '"  ))           
AND T1.CO_EMPR = TMCNTA_BANC.CO_EMPR          
AND T1.CO_BANC = TMCNTA_BANC.CO_BANC          
AND T1.NU_CNTA_BANC = TMCNTA_BANC.NU_CNTA_BANC  ' + @ISCA_WHER_AUXI + '          
AND Exists ( Select CO_EMPR From  TTUSER_CNBA t4          
 Where        T1.CO_EMPR = t4.CO_EMPR          
 And            T1.CO_BANC  = t4.CO_BANC          
 And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC          
 And            t4.CO_USUA = "' + @ISCO_USUA + '"          
 And            t4.ST_REPO = "S")           
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC ')          
          
INSERT INTO #TWPOSI_BANC_Q01_02( CO_EMPR, CO_BANC, NU_CNTA_BANC,           
  IM_INGR, IM_EGRE, IM_CHEQ_CART )          
Select CO_EMPR, CO_BANC, NU_CNTA_BANC,           
 SUM(IM_INGR), SUM(IM_EGRE), SUM(IM_CHEQ_CART)          
FROM #TWPOSI_BANC_Q01_01          
GROUP BY CO_EMPR, CO_BANC, NU_CNTA_BANC          
          
-- INSERTO CUENTAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSABA REPO          
          
INSERT INTO #TWPOSI_BANC_Q01_02( CO_EMPR, CO_BANC, NU_CNTA_BANC,           
  IM_INGR, IM_EGRE, IM_CHEQ_CART )          
SELECT T2.CO_EMPR, T2.CO_BANC, T2.NU_CNTA_BANC, 0, 0, 0          
FROM TMCNTA_BANC T2          
WHERE NOT EXISTS          
(SELECT T1.CO_EMPR FROM #TWPOSI_BANC_Q01_02 T1          
WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)          
AND RTRIM(T1.CO_BANC) = RTRIM(T2.CO_BANC)           
AND RTRIM(T1.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))          
/*************************** FIN REPORTE ****************************/


GO
